using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using DTO;
using System.Collections;

namespace DAOLayer
{
    public class NhanVienDAO
    {
        public DataTable LayBang()
        {
            DataTable dt = new DataTable();
            OleDbConnection cn;
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            cn =Database.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            string strSQL;
            strSQL = "Select * From NhanVien";
            OleDbDataAdapter da;
            da = new OleDbDataAdapter(strSQL, cn);
            da.Fill(dt);
            // B5: Dong ket noi CSDL
            cn.Close();
            return dt;
        }

        public void CapNhatBang(DataTable dt)
        {
            OleDbConnection cn;
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            cn = Database.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            string strSQL;
            strSQL = "Select * From NhanVien";
            OleDbDataAdapter da;
            da = new OleDbDataAdapter(strSQL, cn);
            OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
            da.Update(dt);
            // B5: Dong ket noi CSDL
            cn.Close();
        }

        public DataTable LayDanhSach()
        {
            DataTable dt = new DataTable();
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn;
            cn = Database.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            string strSQL;
            strSQL = "Select * From NhanVien";
            OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn);
            da.Fill(dt);
            cn.Close();
            return dt;

            //OleDbCommand cmd = new OleDbCommand(strSQL, cn);

            //OleDbDataReader dr;
            //dr = cmd.ExecuteReader();
            //while (dr.Read())
            //{
            //    NhanVienDto nvDto = new NhanVienDto();
            //    nvDto.Manv = (string)dr["MaNhanVien"];
            //    nvDto.Tennv = (string)dr["TenNhanVien"];
            //    nvDto.Diachi = (string)dr["DiaChi"];
            //    nvDto.Ngaysinh = (DateTime)dr["NgaySinh"];
            //    nvDto.Dienthoai = (string)dr["DienThoai"];
            //    nvDto.Bangcap = (Int32)dr["BangCap"];
            //    nvDto.Chucvu = (int)dr["ChucVu"];
            //    nvDto.Tinhtrangthe = (int)dr["TinhTrangThe"];
            //    ds.Add(nvDto);
            //}
            //// B5: Dong ket noi CSDL
            //dr.Close();
            //cn.Close();
            //return ds;
        }

        public NhanVienDto TimKiem(string maNhanVien)
        {
            NhanVienDto nvDto = null;            
            OleDbConnection cn;
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            cn = Database.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            string strSQL;
            strSQL = "Select * From NhanVien Where MaNhanVien = ?";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaNhanVien", OleDbType.Integer);
            cmd.Parameters["@MaNhanVien"].Value = maNhanVien;
            OleDbDataReader dr;
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                nvDto = new NhanVienDto();
                nvDto.Manv = (string)dr["MaNhanVien"];
                nvDto.Tennv = (string)dr["TenNhanVien"];
                nvDto.Diachi = (string)dr["DiaChi"];
                nvDto.Ngaysinh = (DateTime)dr["NgaySinh"];
                nvDto.Dienthoai = (string)dr["DienThoai"];
                nvDto.Bangcap = (int)dr["BangCap"];
                nvDto.Chucvu = (int)dr["ChucVu"];
                nvDto.Tinhtrangthe = (int)dr["TinhTrangThe"];
            }
            // B5: Dong ket noi CSDL
            dr.Close();
            cn.Close();
            return nvDto;
        }

        public void Them(NhanVienDto nvDto)
        {
            OleDbConnection cn;
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            cn = Database.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            string strSQL;
            strSQL = "Insert into NhanVien(MaNhanVien, TenNhanVien, DiaChi, NgaySinh, DienThoai, BangCap, ChucVu, TinhTrangThe) values (?, ?, ?, ?, ?, ?, ?, ?)" +
            "";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaNhanVien", OleDbType.WChar);
            cmd.Parameters.Add("@TenNhanVien", OleDbType.WChar);
            cmd.Parameters.Add("@DiaChi", OleDbType.WChar);
            cmd.Parameters.Add("@NgaySinh", OleDbType.Date);
            cmd.Parameters.Add("@DienThoai", OleDbType.WChar);
            cmd.Parameters.Add("@BangCap", OleDbType.Integer);
            cmd.Parameters.Add("@ChucVu", OleDbType.Integer);
            cmd.Parameters.Add("@TinhTrangThe", OleDbType.Integer);

            cmd.Parameters["@MaNhanVien"].Value = nvDto.Manv;
            cmd.Parameters["@TenNhanVien"].Value = nvDto.Tennv;
            cmd.Parameters["@DiaChi"].Value = nvDto.Diachi;
            cmd.Parameters["@NgaySinh"].Value = nvDto.Ngaysinh;
            cmd.Parameters["@DienThoai"].Value = nvDto.Dienthoai;
            cmd.Parameters["@BangCap"].Value = nvDto.Bangcap;
            cmd.Parameters["@ChucVu"].Value =nvDto.Chucvu;
            cmd.Parameters["@TinhTrangThe"].Value = nvDto.Tinhtrangthe;
            cmd.ExecuteNonQuery();
            strSQL = "Select @@IDENTITY";
            cmd = new OleDbCommand(strSQL, cn);
            nvDto.Manv = (string)cmd.ExecuteScalar();
            // B5: Dong ket noi CSDL
            cn.Close();
        }

        public void Xoa(string maNhanVien)
        {
            OleDbConnection cn;
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            cn = Database.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            string strSQL;
            strSQL = "Delete From NhanVien Where MaNhanVien = ? ";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaNhanVien", OleDbType.Integer);
            cmd.Parameters["@MaNhanVien"].Value = maNhanVien;
            cmd.ExecuteNonQuery();
            // B5: Dong ket noi CSDL
            cn.Close();
        }

        public void Sua(NhanVienDto nvDto)
        {
            OleDbConnection cn;
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            cn = Database.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            string strSQL;
            strSQL = "Update NhanVien Set MaNhanVien = ? and TenNhanVien = ? and DiaChi = ? and NgaySinh = ? and DienThoai = ? and BangCap = ? and ChucVu= ? and TinhTrangThe= ? Where MaNhanVien = ?";          
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaNhanVien", OleDbType.WChar);
            cmd.Parameters.Add("@TenNhanVien", OleDbType.WChar);
            cmd.Parameters.Add("@DiaChi", OleDbType.WChar);
            cmd.Parameters.Add("@NgaySinh", OleDbType.Date);
            cmd.Parameters.Add("@DienThoai", OleDbType.WChar);
            cmd.Parameters.Add("@BangCap", OleDbType.Integer);
            cmd.Parameters.Add("@ChucVu", OleDbType.Integer);
            cmd.Parameters.Add("@TinhTrangThe", OleDbType.Integer);


            cmd.Parameters["@MaNhanVien"].Value = nvDto.Manv;
            cmd.Parameters["@TenNhanVien"].Value = nvDto.Tennv;
            cmd.Parameters["@DiaChi"].Value = nvDto.Diachi;
            cmd.Parameters["@NgaySinh"].Value = nvDto.Ngaysinh;
            cmd.Parameters["@DienThoai"].Value = nvDto.Dienthoai;
            cmd.Parameters["@BangCap"].Value = nvDto.Bangcap;
            cmd.Parameters["@ChucVu"].Value = nvDto.Chucvu;
            cmd.Parameters["@TinhTrangThe"].Value = nvDto.Tinhtrangthe;
            cmd.ExecuteNonQuery();
            // B5: Dong ket noi CSDL
            cn.Close();
        }
    }
}
